--�}Ŀ1��10�֣���ʹ��PowerDesigner����ƿ���ʹ�õ�SqlServer���ݿ�����ģ�ͣ�Ҫ���������ƺ�Ӣ�����������Ӣ�Ĳ�֪���Ŀ��Խ�������������������ϵ׼ȷ��������ƺõ�ģ������sql��䣬����һ����ΪOADatabase�����ݿ⣬���ã�
create database OADatabase
go
use OADatabase
go
/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2012                    */
/* Created on:     2021/10/26 9:22:29                           */
/*==============================================================*/


if exists (select 1
            from  sysobjects
           where  id = object_id('DepartmentInfo')
            and   type = 'U')
   drop table DepartmentInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PlanningTasks')
            and   type = 'U')
   drop table PlanningTasks
go

if exists (select 1
            from  sysobjects
           where  id = object_id('StaffInfo')
            and   type = 'U')
   drop table StaffInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserApplyForInfo')
            and   type = 'U')
   drop table UserApplyForInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserInfo')
            and   type = 'U')
   drop table UserInfo
go

/*==============================================================*/
/* Table: DepartmentInfo                                        */
/*==============================================================*/
create table DepartmentInfo (
   DepartmentID         int                  not null,
   DepartmentName       nvarchar(30)         null,
   Supervisor           nvarchar(30)         null,
   Telephone            nvarchar(30)         null,
   Email                nvarchar(30)         null,
   constraint PK_DEPARTMENTINFO primary key (DepartmentID)
)
go

/*==============================================================*/
/* Table: PlanningTasks                                         */
/*==============================================================*/
create table PlanningTasks (
   ApplyID              int                  identity,
   PlanToDo             nvarchar(30)         null,
   PlanToDate           date                 null,
   constraint PK_PLANNINGTASKS primary key (ApplyID)
)
go

/*==============================================================*/
/* Table: StaffInfo                                             */
/*==============================================================*/
create table StaffInfo (
   StaffID              int                  identity,
   StaffName            nvarchar(30)         null,
   post                 nvarchar(30)         null,
   telephone            nvarchar(30)         null,
   pay                  decimal              null,
   constraint PK_STAFFINFO primary key (StaffID)
)
go

/*==============================================================*/
/* Table: UserApplyForInfo                                      */
/*==============================================================*/
create table UserApplyForInfo (
   ApplyID              int                  identity,
   UserID               int                  null,
   MeetingDate          date                 null,
   CoutDate             int                  null,
   CoutPerson           int                  null,
   Part                 nvarchar(30)         null,
   Budget               decimal              null,
   AcceptLogo           nvarchar(30)         null,
   constraint PK_USERAPPLYFORINFO primary key (ApplyID)
)
go

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
   UserID               int                  identity,
   UserName             nvarchar(30)         null,
   BankAccount          nvarchar(30)         null,
   Telephone            nvarchar(30)         null,
   Address              nvarchar(30)         null,
   constraint PK_USERINFO primary key (UserID)
)
go

--��Ŀ2��10�֣���Ϊ�˱�֤���û�������飬������־Ϊ �����룬�����һ�������������û�������Ϣ���������ݵ�ʱ�򣬿����ж���Ĭ��������־�Ƿ�Ϊ�������룬����ǣ������账�������������룬������������¸���������־Ϊ�������룻
create trigger tri_UserApplyForInfo
on
UserApplyForInfo
after
insert
as
begin
	if(AcceptLogo='������')
		print '������'
end
go

--��Ŀ3��10�֣�����ҵ��Ա�����û������ʱ����Ҫ����ǰ�����ļ�¼��������־�޸�Ϊ����������ͬʱ����������״̬Ϊ�������룬�һ��������ѳ����ļ�¼�е�������־����Ϊ���ѹ��ڣ������һ���洢������������ҵ��
create proc proc_AlterUserApplyForInfo
@ApplyID int
as
begin
	update  UserApplyForInfo set AcceptLogo='������' where ApplyID = @ApplyID
	update  UserApplyForInfo set AcceptLogo='������' where ApplyID != @ApplyID
end
go
exec proc_AlterUserApplyForInfo 1 